package com.example.neusoft.controller;

import com.example.neusoft.entity.Excel;
import com.example.neusoft.service.ExcelService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

/**
 * (Excel)表控制层
 * @author makejava
 * @since 2020-05-25 21:48:18
 */
@Controller
public class ExcelController {

    @Autowired
    private ExcelService excelService;

    /**
     * 通过主键查询单条数据
     * @param id 主键
     * @return 单条数据
     */
    @GetMapping("selectOne")
    @ResponseBody
    public Excel selectOne(Integer id) {
        return this.excelService.queryById(id);
    }

    @PostMapping("/uploadExcel")
    @ResponseBody
    public String uploadExcel() throws IOException {
        //实际这个路径由前端传后台
        File file = new File("D:\\17外包第6学期实训安排.xlsx");
        FileInputStream fis = new FileInputStream(file);
        Workbook wb = null;
        try {
            if(isExcel2003(file.getPath())){
                System.out.println("2003版本Excel: .xls结尾");
                //创建2003版本Excel工作簿对象
                wb = new HSSFWorkbook(fis);
            }else if (isExcel2007(file.getPath())){
                System.out.println("2007版本Excel: .xlsx结尾");
                //创建2007版本Excel工作簿对象
                wb = new XSSFWorkbook(fis);
            }else {
                System.out.println("未知版本的Excel !!!");
            }
            //获取第1个工作表
            Sheet sheet = wb.getSheetAt(0);
            Row row = sheet.getRow(0);
            Cell cell = row.getCell(0);
            //循环Excel文件的i=1行开始
            for(int i=2; i<=sheet.getLastRowNum(); i++) {
                Excel excel = new Excel();
                row = sheet.getRow(i);
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String data = cell.getStringCellValue();
                        data = data.trim();
                        if (j == 0) {
                            excel.setId(Integer.parseInt(data));
                        } else if (j == 1) {
                            excel.setSno(Integer.parseInt(data));
                        } else if (j == 2) {
                            excel.setName(data);
                        } else if (j == 3) {
                            excel.setClassname(data);
                        } else if (j == 4) {
                            excel.setNotes(data);
                        }
                    }
                }
                excelService.insert(excel);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return "false";
        }finally {
            fis.close();
        }
        return "true";
    }

    public static boolean isExcel2003(String filePath)
    {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath)
    {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
}